{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "022aab2d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8f17152c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "L1=pd.read_excel('./18级高一体测成绩汇总.xls')\n",
    "L2=pd.read_excel('./18级高一体测成绩汇总.xls',sheet_name = 1)\n",
    "L3=pd.read_excel('./体侧成绩评分表.xls',header = [0,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e9f6bbf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "L1=L1.replace(\"'\",'.',regex=True)\n",
    "L2=L2.replace(\"'\",'.',regex=True)\n",
    "L3=L3.replace(\"'\",'.',regex=True).replace('\"',\"\",regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f798e11d",
   "metadata": {},
   "outputs": [],
   "source": [
    "L1.iloc[:,2:]=L1.iloc[:,2:].applymap(lambda x:float(x))\n",
    "L2.iloc[:,2:]=L2.iloc[:,2:].applymap(lambda x:float(x))\n",
    "L3=L3.applymap(lambda x:float(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "b522a6ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "L3=L3.fillna(value=99999)\n",
    "#将所有空数据替换为99999，便于在接下来每列数据的遍历中，判断是否有空数据存在"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5f317495",
   "metadata": {},
   "outputs": [],
   "source": [
    "def scoreAuto(sex):\n",
    "    if sex=='男':\n",
    "        list=L1\n",
    "    if sex=='女':\n",
    "        list=L2\n",
    "    i=1#插入列的初始位置\n",
    "    for item in list.columns:\n",
    "        if item==\"班级\" or item==\"性别\"or item==\"身高\"or item==\"体重\":\n",
    "            i+=1\n",
    "            continue\n",
    "        if item==\"男1000米跑\"or item==sex+\"50米跑\"or item==\"女800米跑\":\n",
    "            grades=L3[(item,\"成绩\")].values.tolist()\n",
    "            grades.insert(0,0)\n",
    "            score=L3[(item,\"分数\")].values.tolist()\n",
    "            list.insert(loc = i,column=item+'分数',value= pd.cut(list[item],bins = grades,labels= score))\n",
    "            i+=2\n",
    "            continue\n",
    "        if item==\"BMI\":\n",
    "            list['BMI']=list['体重']/list['身高']\n",
    "            continue\n",
    "        else:\n",
    "            grades=L3[(item,\"成绩\")].values.tolist()\n",
    "            score=L3[(item,\"分数\")].values.tolist() \n",
    "            checkNull=0\n",
    "            nullpath=[]\n",
    "            for searchNull in grades:\n",
    "                if searchNull ==99999:\n",
    "                    del score[checkNull]\n",
    "                    nullpath.append(checkNull)\n",
    "                else:\n",
    "                    checkNull+=1\n",
    "            if nullpath:\n",
    "                for n in nullpath:\n",
    "                    del grades[n]\n",
    "    #查找成绩中是否有空数据存在，若有，则将与其对应的分数一并删除\n",
    "            grades.sort()\n",
    "            score.sort()\n",
    "            checkZero=0\n",
    "    #         for searchZero in grades:\n",
    "    #             if searchZero ==0:\n",
    "    #                 checkZero=1\n",
    "    #                 break\n",
    "    #         if checkZero==0:\n",
    "    #             grades.insert(0,0)\n",
    "    #         if checkZero ==1:\n",
    "    #             grades.insert(0,-100) \n",
    "            grades.append(99999)  #近似设置一个类似于正无穷的值\n",
    "            list.insert(loc = i,column=item+'分数',value= pd.cut(list[item],bins = grades,labels= score,right=1))\n",
    "            i=i+2\n",
    "#     这里为什么不能用list=list.fillna(value=0)\n",
    "    return list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "5f59c149",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>男1000米跑</th>\n",
       "      <th>男1000米跑分数</th>\n",
       "      <th>男50米跑</th>\n",
       "      <th>男50米跑分数</th>\n",
       "      <th>男跳远</th>\n",
       "      <th>男跳远分数</th>\n",
       "      <th>男体前屈</th>\n",
       "      <th>男体前屈分数</th>\n",
       "      <th>男引体</th>\n",
       "      <th>男引体分数</th>\n",
       "      <th>男肺活量</th>\n",
       "      <th>男肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.13</td>\n",
       "      <td>72.0</td>\n",
       "      <td>8.88</td>\n",
       "      <td>66.0</td>\n",
       "      <td>195.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2785.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>170.0</td>\n",
       "      <td>72.6</td>\n",
       "      <td>0.427059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.16</td>\n",
       "      <td>70.0</td>\n",
       "      <td>7.70</td>\n",
       "      <td>78.0</td>\n",
       "      <td>225.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>3133.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>174.0</td>\n",
       "      <td>52.7</td>\n",
       "      <td>0.302874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.09</td>\n",
       "      <td>74.0</td>\n",
       "      <td>8.45</td>\n",
       "      <td>70.0</td>\n",
       "      <td>218.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3901.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>169.0</td>\n",
       "      <td>46.5</td>\n",
       "      <td>0.275148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>4.21</td>\n",
       "      <td>68.0</td>\n",
       "      <td>8.05</td>\n",
       "      <td>74.0</td>\n",
       "      <td>206.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4946.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>183.0</td>\n",
       "      <td>79.7</td>\n",
       "      <td>0.435519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>男</td>\n",
       "      <td>3.44</td>\n",
       "      <td>85.0</td>\n",
       "      <td>7.52</td>\n",
       "      <td>78.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>3538.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>171.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>0.319883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.23</td>\n",
       "      <td>68.0</td>\n",
       "      <td>8.27</td>\n",
       "      <td>72.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4647.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>176.0</td>\n",
       "      <td>69.5</td>\n",
       "      <td>0.394886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>5.19</td>\n",
       "      <td>40.0</td>\n",
       "      <td>9.55</td>\n",
       "      <td>50.0</td>\n",
       "      <td>210.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>7042.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>0.429379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>3.25</td>\n",
       "      <td>100.0</td>\n",
       "      <td>7.50</td>\n",
       "      <td>80.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>5755.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>0.359116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>4.39</td>\n",
       "      <td>62.0</td>\n",
       "      <td>7.81</td>\n",
       "      <td>76.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>5688.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>51.7</td>\n",
       "      <td>0.300581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>17</td>\n",
       "      <td>男</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>477 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  男1000米跑 男1000米跑分数  男50米跑 男50米跑分数    男跳远 男跳远分数  男体前屈 男体前屈分数   男引体  \\\n",
       "0     1  男     4.13      72.0   8.88    66.0  195.0  50.0  12.0   74.0   1.0   \n",
       "1     1  男     4.16      70.0   7.70    78.0  225.0  74.0  11.0   74.0   7.0   \n",
       "2     1  男     4.09      74.0   8.45    70.0  218.0  70.0  14.0   78.0   1.0   \n",
       "3     1  男     4.21      68.0   8.05    74.0  206.0  64.0  13.0   76.0   1.0   \n",
       "4     1  男     3.44      85.0   7.52    78.0  210.0  66.0  13.0   76.0   9.0   \n",
       "..   .. ..      ...       ...    ...     ...    ...   ...   ...    ...   ...   \n",
       "472  17  男     4.23      68.0   8.27    72.0  208.0  66.0  10.0   72.0   0.0   \n",
       "473  17  男     5.19      40.0   9.55    50.0  210.0  66.0  15.0   78.0   6.0   \n",
       "474  17  男     3.25     100.0   7.50    80.0  252.0  90.0  13.0   76.0  13.0   \n",
       "475  17  男     4.39      62.0   7.81    76.0  208.0  66.0  14.0   78.0  11.0   \n",
       "476  17  男     0.00       NaN   0.00     NaN    0.0   NaN   0.0   40.0   0.0   \n",
       "\n",
       "    男引体分数    男肺活量 男肺活量分数     身高    体重       BMI  \n",
       "0     NaN  2785.0   62.0  170.0  72.6  0.427059  \n",
       "1    50.0  3133.0   68.0  174.0  52.7  0.302874  \n",
       "2     NaN  3901.0   80.0  169.0  46.5  0.275148  \n",
       "3     NaN  4946.0  100.0  183.0  79.7  0.435519  \n",
       "4    64.0  3538.0   74.0  171.0  54.7  0.319883  \n",
       "..    ...     ...    ...    ...   ...       ...  \n",
       "472   NaN  4647.0  100.0  176.0  69.5  0.394886  \n",
       "473  40.0  7042.0  100.0  177.0  76.0  0.429379  \n",
       "474  80.0  5755.0  100.0  181.0  65.0  0.359116  \n",
       "475  72.0  5688.0  100.0  172.0  51.7  0.300581  \n",
       "476   NaN     0.0    NaN    0.0   0.0       NaN  \n",
       "\n",
       "[477 rows x 17 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "L1=scoreAuto('男')\n",
    "L1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "6e521434",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>班级</th>\n",
       "      <th>性别</th>\n",
       "      <th>女800米跑</th>\n",
       "      <th>女800米跑分数</th>\n",
       "      <th>女50米跑</th>\n",
       "      <th>女50米跑分数</th>\n",
       "      <th>女跳远</th>\n",
       "      <th>女跳远分数</th>\n",
       "      <th>女体前屈</th>\n",
       "      <th>女体前屈分数</th>\n",
       "      <th>女仰卧</th>\n",
       "      <th>女仰卧分数</th>\n",
       "      <th>女肺活量</th>\n",
       "      <th>女肺活量分数</th>\n",
       "      <th>身高</th>\n",
       "      <th>体重</th>\n",
       "      <th>BMI</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.22</td>\n",
       "      <td>100.0</td>\n",
       "      <td>9.32</td>\n",
       "      <td>72.0</td>\n",
       "      <td>185.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>3775.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>51.3</td>\n",
       "      <td>0.314724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>4.59</td>\n",
       "      <td>40.0</td>\n",
       "      <td>11.44</td>\n",
       "      <td>10.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>3683.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>163.0</td>\n",
       "      <td>66.6</td>\n",
       "      <td>0.408589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.46</td>\n",
       "      <td>80.0</td>\n",
       "      <td>13.40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>3331.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>0.382166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.39</td>\n",
       "      <td>85.0</td>\n",
       "      <td>9.52</td>\n",
       "      <td>70.0</td>\n",
       "      <td>172.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>3701.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>160.0</td>\n",
       "      <td>50.7</td>\n",
       "      <td>0.316875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>女</td>\n",
       "      <td>3.43</td>\n",
       "      <td>85.0</td>\n",
       "      <td>9.79</td>\n",
       "      <td>68.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>3592.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>167.0</td>\n",
       "      <td>63.9</td>\n",
       "      <td>0.382635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>588</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.51</td>\n",
       "      <td>78.0</td>\n",
       "      <td>9.60</td>\n",
       "      <td>70.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0.310127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>589</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.00</td>\n",
       "      <td>76.0</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>2937.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>0.345963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>3.45</td>\n",
       "      <td>80.0</td>\n",
       "      <td>10.18</td>\n",
       "      <td>64.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>2592.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>0.294545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>591</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.01</td>\n",
       "      <td>74.0</td>\n",
       "      <td>9.67</td>\n",
       "      <td>68.0</td>\n",
       "      <td>165.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>1829.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>154.0</td>\n",
       "      <td>43.6</td>\n",
       "      <td>0.283117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>17</td>\n",
       "      <td>女</td>\n",
       "      <td>4.48</td>\n",
       "      <td>50.0</td>\n",
       "      <td>9.09</td>\n",
       "      <td>74.0</td>\n",
       "      <td>180.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>2962.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>162.0</td>\n",
       "      <td>55.3</td>\n",
       "      <td>0.341358</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>593 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     班级 性别  女800米跑 女800米跑分数  女50米跑 女50米跑分数    女跳远 女跳远分数  女体前屈 女体前屈分数   女仰卧  \\\n",
       "0     1  女    3.22    100.0   9.32    72.0  185.0  80.0  16.0   76.0  48.0   \n",
       "1     1  女    4.59     40.0  11.44    10.0  148.0  50.0   9.0   66.0  29.0   \n",
       "2     1  女    3.46     80.0  13.40     NaN  150.0  60.0   7.0   62.0  40.0   \n",
       "3     1  女    3.39     85.0   9.52    70.0  172.0  74.0  21.0   90.0  46.0   \n",
       "4     1  女    3.43     85.0   9.79    68.0  145.0  50.0   8.0   64.0  34.0   \n",
       "..   .. ..     ...      ...    ...     ...    ...   ...   ...    ...   ...   \n",
       "588  17  女    3.51     78.0   9.60    70.0  150.0  60.0  24.0   95.0  41.0   \n",
       "589  17  女    4.00     76.0  10.18    64.0  150.0  60.0  13.0   72.0  36.0   \n",
       "590  17  女    3.45     80.0  10.18    64.0  152.0  62.0  15.0   76.0  35.0   \n",
       "591  17  女    4.01     74.0   9.67    68.0  165.0  70.0  10.0   68.0  41.0   \n",
       "592  17  女    4.48     50.0   9.09    74.0  180.0  80.0  10.0   68.0  46.0   \n",
       "\n",
       "    女仰卧分数    女肺活量 女肺活量分数     身高    体重       BMI  \n",
       "0    85.0  3775.0  100.0  163.0  51.3  0.314724  \n",
       "1    64.0  3683.0  100.0  163.0  66.6  0.408589  \n",
       "2    76.0  3331.0  100.0  157.0  60.0  0.382166  \n",
       "3    80.0  3701.0  100.0  160.0  50.7  0.316875  \n",
       "4    70.0  3592.0  100.0  167.0  63.9  0.382635  \n",
       "..    ...     ...    ...    ...   ...       ...  \n",
       "588  76.0  2255.0   70.0  158.0  49.0  0.310127  \n",
       "589  72.0  2937.0   85.0  161.0  55.7  0.345963  \n",
       "590  70.0  2592.0   76.0  165.0  48.6  0.294545  \n",
       "591  76.0  1829.0   60.0  154.0  43.6  0.283117  \n",
       "592  80.0  2962.0   85.0  162.0  55.3  0.341358  \n",
       "\n",
       "[593 rows x 17 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "L2=scoreAuto('女')\n",
    "L2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "46d5d2a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "with pd.ExcelWriter('./Homework.xlsx') as writer:\n",
    "    L1.to_excel(writer,sheet_name='男',index = False)\n",
    "    L2.to_excel(writer,sheet_name='女',index = False)\n",
    "L1=pd.read_excel('./Homework.xlsx')\n",
    "L2=pd.read_excel('./Homework.xlsx',sheet_name = 1)\n",
    "L1=L1.fillna(value=0)\n",
    "L1=L1.fillna(value=0)\n",
    "with pd.ExcelWriter('./Homework.xlsx') as writer:\n",
    "    L1.to_excel(writer,sheet_name='男',index = False)\n",
    "    L2.to_excel(writer,sheet_name='女',index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
